using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using DTOLayer;

namespace DAOLayer
{
    public class NhaXuatBanDAO:AbstractDao
    {
        public int TestExisted(NhaXuatBanDTO dto)
        {
            SqlConnection cn;
            cn = this.SqlConnectiondata();
            int kq = -1;
            try
            {
                cn.Open();
                String str = "select count(*) from NhaXuatBan where TenNhaXuatBan = ? and DiaChi = ? and DienThoai = ? ";
                SqlCommand cmd = new SqlCommand(str, cn);
                cmd.Parameters.Add("@TenNhaXuatBan", SqlDbType.NChar);
                cmd.Parameters["@TenNhaXuatBan"].Value = dto.TenNhaXuatBan;
                cmd.Parameters.Add("@DiaChi", SqlDbType.NChar);
                cmd.Parameters["@DiaChi"].Value = dto.DiaChi;
                cmd.Parameters.Add("@DienThoai", SqlDbType.NChar);
                cmd.Parameters["@DienThoai"].Value = dto.DienThoai;
                if ((int)cmd.ExecuteScalar() == 0)
                    kq = 1;
                return kq;
            }
            catch(Exception e)
            {
                return kq;
            }
            finally
            {
                cn.Close();
            }
        }
        public int InsertNhaXuatBan(NhaXuatBanDTO dto)
        {
            int kq = TestExisted(dto);
            if (kq == -1)
                return -1;
            SqlConnection cn;
            cn = this.SqlConnectiondata();
            try
            {
                cn.Open();
                String str = "Insert into NhaXuatBan (TenNhaXuatBan,DiaChi,DienThoai,Email) values (?,?,?,?)";
                SqlCommand cmd = new SqlCommand(str, cn);                
                cmd.Parameters.Add("@TenNhaXuatBan", SqlDbType.NChar);
                cmd.Parameters["@TenNhaXuatBan"].Value = dto.TenNhaXuatBan;
                cmd.Parameters.Add("@DiaChi", SqlDbType.NChar);
                cmd.Parameters["@DiaChi"].Value = dto.DiaChi;
                cmd.Parameters.Add("@DienThoai", SqlDbType.NChar);
                cmd.Parameters["@DienThoai"].Value = dto.DienThoai;
                cmd.Parameters.Add("@Email", SqlDbType.NChar);
                cmd.Parameters["@Email"].Value = dto.Email;
                cmd.ExecuteNonQuery();
                str = "Select @@IDENTITY";
                cmd = new SqlCommand(str, cn);
                dto.MaNhaXuatBan = (int)cmd.ExecuteScalar();
                return dto.MaNhaXuatBan;
            }
            catch (Exception e)
            {
                return -1;
            }
            finally
            {
                cn.Close();
            }
        }
        public int DeleteNhaXuatBan(int manxb)
        {
            SqlConnection cn;
            cn = this.SqlConnectiondata();
            try
            {
                cn.Open();
                String str = "Delete from NhaXuatBan where MaNhaXuatBan = ?";
                SqlCommand cmd = new SqlCommand(str, cn);
                cmd.Parameters.Add("@MaNhaXuatBan", SqlDbType.Int);
                cmd.Parameters["@MaNhaXuatBan"].Value = manxb;
                cmd.ExecuteNonQuery();
                return 1;
            }
            catch (Exception e)
            {
                return -1;
            }
            finally
            {
                cn.Close();
            }
        }
        public int UpdateNhaXuatBan(NhaXuatBanDTO dto)
        {
            SqlConnection cn;
            cn = this.SqlConnectiondata();
            try
            {
                cn.Open();
                String str = "Update NhaXuatBan set TenNhaXuatBan = ?, DiaChi = ? , DienThoai = ?, Email = ? where MaNhaXuatBan = ?";
                SqlCommand cmd = new SqlCommand(str, cn);
                cmd.Parameters.Add("@TenNhaXuatBan", SqlDbType.NChar);
                cmd.Parameters["@TenNhaXuatBan"].Value = dto.TenNhaXuatBan;
                cmd.Parameters.Add("@DiaChi", SqlDbType.NChar);
                cmd.Parameters["@DiaChi"].Value = dto.DiaChi;
                cmd.Parameters.Add("@DienThoai", SqlDbType.NChar);
                cmd.Parameters["@DienThoai"].Value = dto.DienThoai;
                cmd.Parameters.Add("@Email", SqlDbType.NChar);
                cmd.Parameters["@Email"].Value = dto.Email;
                cmd.Parameters.Add("@MaNhaXuatBan", SqlDbType.Int);
                cmd.Parameters["@MaNhaXuatBan"].Value = dto.MaNhaXuatBan;
                cmd.ExecuteNonQuery();
                return 1;
            }
            catch (Exception e)
            {
                return -1;
            }
            finally
            {
                cn.Close();
            }
        }
        public DataTable LayDanhSach()
        {
            DataTable dt = new DataTable();
            try
            {
                SqlConnection cn;
                cn = this.SqlConnectiondata();
                cn.Open();
                String str = "select * from NhaXuatBan";

                SqlDataAdapter d = new SqlDataAdapter(str, cn);
                d.Fill(dt);
                cn.Close();

            }
            catch (Exception e)
            {
                return null;
            }
            return dt;
        }
    }
}
